package com.cykj.system.service.impl;

import com.cykj.common.core.domain.entity.SysDept;
import com.cykj.system.service.DynamicBuildTableService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author SunJinDuo
 * @date 2022/7/18
 */
@Service
public class DynamicBuildTableServiceImpl implements DynamicBuildTableService {
    @Value("${sql.jdbc}")
    private String jdbc;

    @Value("${sql.url}")
    private String url;

    @Value("${sql.user}")
    private String user;

    @Value("${sql.pass}")
    private String pass;
    /**
     *  测试动态建表
     */
    @Override
    public void buildTable(String suffix, String deptName) throws Exception {
        // 添加表字段
        List<String> fileds = this.getFileds();
        // 创建表名称
        String tableName = "biz_transaction_amount_" + suffix;
        // 生成表
        dynamicCreateTableFileds(tableName, fileds, deptName);
    }

    /**
     *  测试动态建表
     */
        private void dynamicCreateTableFileds(String table, List<String> fileds, String notes) throws Exception{
        // 连接数据库
        Class.forName(jdbc);
        Connection conn = DriverManager.getConnection(url, user, pass);
        Statement stat = conn.createStatement();
        // 拼接创建表的sql
        String sql =  "CREATE TABLE "+ table +"(";
        for (String str:fileds) {
            sql = sql+str;
        }
        sql = sql + " ) charset=utf8 COMMENT = '" + notes + "_交易通知表'; ";
        //获取数据库表名
        ResultSet rs = conn.getMetaData().getTables(null, null, table, null);
        // 判断表是否存在，如果存在则什么都不做，否则创建表
        if(!rs.next() ){
            stat.executeLargeUpdate(sql);
        }
        //查询数据(测试是否建表成功)
        ResultSet result = stat.executeQuery("select * from "+table);
        while (result.next()) {
            System.out.println(result.getInt("id") + " " + result.getString("hname"));
        }
        //关闭数据库
        result.close();
        stat.close();
        conn.close();
    }

    /**
     *  添加字段
     */
    public List<String> getFileds() {
        List<String> fileds = new ArrayList<>();
        fileds.add(" id bigint(20) not null PRIMARY KEY AUTO_INCREMENT COMMENT '主键自增',");
        fileds.add(" host_date datetime(0) DEFAULT NULL COMMENT '系统日期',");
        fileds.add(" host_time varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '系统时间',");
        fileds.add(" merchant_no varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '系统商户号',");
        fileds.add(" terminal_no varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '系统终端号',");
        fileds.add(" pan varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '交易卡号（脱敏）',");
        fileds.add(" amnount double(11, 2) DEFAULT NULL COMMENT '交易金额',");
        fileds.add(" addi_amount double(11, 2) DEFAULT NULL COMMENT '交易手续费',");
        fileds.add(" fee_name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '手续费名称',");
        fileds.add(" card_type int(8) DEFAULT NULL COMMENT '卡类型',");
        fileds.add(" trans_type int(8) DEFAULT NULL COMMENT '交易编码',");
        fileds.add(" enter_mode varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '输入方式',");
        fileds.add(" pos_sn varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'SN',");
        fileds.add(" pos_trace varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'pos流水号',");
        fileds.add(" chl_date varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '渠道交易日期',");
        fileds.add(" chl_time varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '渠道交易时间',");
        fileds.add(" chl_merchant_no varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '渠道商户号',");
        fileds.add(" chl_terminal_no varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '渠道终端号',");
        fileds.add(" retrirefnum varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '参考号',");
        fileds.add(" auth_code varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '授权码',");
        fileds.add(" host_ret_code varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '交易状态',");
        fileds.add(" acp_ret_code varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '清算状态',");
        fileds.add(" bank_code varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '银行代码',");
        fileds.add(" acq_certificate varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '入账人身份证（脱敏）',");
        fileds.add(" scan_merchant_no varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '扫码商户号',");
        fileds.add(" acq_name varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '入账人',");
        fileds.add(" acq_account_no varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '入账卡号',");
        fileds.add(" scan_mch_id varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '扫码门店号',");
        fileds.add(" order_no varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '订单编号',");
        fileds.add(" platform_no varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '平台订单编号',");
        fileds.add(" merch_id bigint(20) DEFAULT NULL COMMENT '商户id',");
        fileds.add(" uuid bigint(20) DEFAULT NULL COMMENT '关联分润明细id',");
        fileds.add(" whether_steal int(1) DEFAULT 0 COMMENT '是否偷单 ， 0：不偷，1：偷',");
        fileds.add(" parent_id bigint(20) DEFAULT NULL COMMENT '商户上级代理id',");
        fileds.add(" sim_amt int(8) DEFAULT NULL COMMENT '流量费金额',");
        fileds.add(" tran_sim int(1) DEFAULT 0 COMMENT '流量费收取状态 1 收取',");
        fileds.add(" date_type varchar(10) COMMENT '0：T0 1：T1' ,");
        fileds.add(" INDEX sn(pos_sn) USING BTREE ");
        return fileds;
    }
}
